/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Database;

import Domain.Customer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author Min0
 */
public class DBCustomer implements IFDBCustomer {
    
    private Connection con;
    
    public DBCustomer()
    {
        con = DBConnection.getInstance().getDBcon();
    }
    
    //START private methods for class
    private Customer BuildApartment(ResultSet results)
    {
        Customer appObj = new Customer();
        try
        {
            appObj.setLoginID(results.getString("LoginID"));
            appObj.setName(results.getString("Name"));
            appObj.setAddress(results.getString("Address"));
            appObj.setCountry(results.getString("Country"));
            appObj.setPhoneNo(results.getString("PhoneNo"));
            appObj.setEmail(results.getString("Email"));
            appObj.setBill(results.getFloat("Bill"));
            appObj.setDeposit(results.getFloat("Deposit"));
            appObj.setBookedActivities(results.getInt("BookedActivities"));
            appObj.setMaxBookedActivitiesPerDay(results.getInt("MaxBookedActivities"));
        } catch (Exception e)
        {
            System.out.println("error in building the customer object\n" + e.getMessage());
        }
        return appObj;
    }

    private String BuildQuery(String wClause)
    {
        String query = "SELECT * FROM HCustomer";

        if (wClause.length() > 0)
        {
            query = query + " WHERE " + wClause;
        }

        return query;
    }

    private Customer singleWhere(String wClause)
    {
        ResultSet results;
        Customer appObj = new Customer();
        String query = BuildQuery(wClause);
        System.out.println(query);
        try
        {
            java.sql.Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next())
            {
                appObj = BuildApartment(results);
                stmt.close();
            }

        }//end try	
        catch (Exception e)
        {
            System.out.println("Query exception: " + e);
        }
        return appObj;
    }

    private ArrayList<Customer> miscWhere(String wClause)
    {
        ResultSet results;
        ArrayList<Customer> list = new ArrayList<Customer>();

        String query = BuildQuery(wClause);

        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next())
            {
                Customer appObj = new Customer();
                appObj = BuildApartment(results);
                list.add(appObj);
            }//end while
            stmt.close();
        } catch (Exception e)
        {
            System.out.println("Query exception - select: " + e);
        }
        return list;
    }
    //END private methods for class

    @Override
    public void InsertCustomer(Customer customer)
    {
        String query = "INSERT INTO HCustomer (LoginID, Name, Address, Country, PhoneNo, Email, Bill, Deposit, BookedActivities, MaxBookedActivities) VALUES ('"
                + customer.getLoginID() + "','"
                + customer.getName() + "','"
                + customer.getAddress() + "','"
                + customer.getCountry() + "','"
                + customer.getPhoneNo() + "','"
                + customer.getEmail() + "',"
                + customer.getBill() + ","
                + customer.getDeposit() + ","
                + customer.getBookedActivities() + ","
                + customer.getMaxBookedActivitiesPerDay() +
                ")";
        
        System.out.println("insert: " + query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        }//end try	
        catch (Exception ex)
        {
            System.out.println("Insert exception in customer db: " + ex);
        }
                
    }

    @Override
    public void InsertCustomer(String LoginID, String Name, String Address, String Country, String PhoneNo, String Email, float Bill, float Deposit, int BookedActivities, int MaxBookedActivities)
    {
        String query = "INSERT INTO HCustomer (LoginID, Name, Address, Country, PhoneNo, Email, Bill, Deposit, BookedActivities, MaxBookedActivities) VALUES ('"
                + LoginID + "','"
                + Name + "','"
                + Address + "','"
                + Country + "','"
                + PhoneNo + "','"
                + Email + "',"
                + Bill + ","
                + Deposit + ","
                + BookedActivities + ","
                + MaxBookedActivities +
                ")";
        
        System.out.println("insert: " + query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        }//end try	
        catch (Exception ex)
        {
            System.out.println("Insert exception in customer db: " + ex);
        }
    }

    @Override
    public Customer GetCustomer(String LoginID)
    {
        String wClause = "LoginID = '" + LoginID + "'";
        return singleWhere(wClause);
    }

    @Override
    public ArrayList<Customer> GetCustomerList()
    {
        return miscWhere("");
    }

    @Override
    public void UpdateCustomer(String LoginID, String Name, String Address, String Country, String PhoneNo, String Email, float Bill, float Deposit, int BookedActivities, int MaxBookedActivities)
    {
        String query = "UPDATE HCustomer SET "
                + "Name = '" + Name + "',"
                + "Address ='" + Address + "',"
                + "Country = '" + Country + "',"
                + "PhoneNo = '" + PhoneNo + "',"
                + "Email = '" + Email + "',"
                + "Bill = " + Bill + ","
                + "Deposit = " + Deposit + ","
                + "BookedActivities = " + BookedActivities + ","
                + "MaxBookedActivities = " + MaxBookedActivities
                + " WHERE LoginID = '" + LoginID + "'";
        System.out.println("UPDATE QUERY: "+query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        } catch (Exception ex)
        {
            System.out.println("Update exception in customer db: " + ex);
        }
    }

    @Override
    public void DeleteCustomer(String LoginID)
    {
        String query = "DELETE FROM HCustomer WHERE LoginID = '"
                + LoginID + "'";
        System.out.println(query);
        try
        { // delete from customer
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        } catch (Exception ex)
        {
            System.out.println("Delete exception in customer db: " + ex);
        }
    }

    @Override
    public void UpdateCustomer(String LoginID, float Deposit)
    {
        String query = "UPDATE HCustomer SET "
                + "Deposit = " + Deposit
                + " WHERE LoginID = '" + LoginID + "'";
        System.out.println("UPDATE QUERY: "+query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        } catch (Exception ex)
        {
            System.out.println("Update exception in customer db: " + ex);
        }
    }

    @Override
    public void UpdateCustomer(String LoginID, int BookedActivities)
    {
        String query = "UPDATE HCustomer SET "
                + "BookedActivities = " + BookedActivities
                + " WHERE LoginID = '" + LoginID + "'";
        System.out.println("UPDATE QUERY: "+query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        } catch (Exception ex)
        {
            System.out.println("Update exception in customer db: " + ex);
        }
    }

    @Override
    public int getMax()
    {
        int max = getMax.getMaxId("Select max(LoginID) from HCustomer");
        return max;
    }

    @Override
    public void UpdateCustomerBill(String LoginID, float amount)
    {
        float newBill = GetCustomer(LoginID).getBill()+amount;
        String query = "UPDATE HCustomer SET "
                + "Bill = " + newBill
                + " WHERE LoginID = '" + LoginID + "'";
        System.out.println("UPDATE QUERY: "+query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        } catch (Exception ex)
        {
            System.out.println("Update exception in customer db: " + ex);
        }
    }
}
